using System.Collections.Generic;
using Excel;
using System.Data;
using System.IO;
using System.Text;
using System;
using System.Text.RegularExpressions;
using EntityKit.Runtime;
using UnityEngine;

namespace EntityKit.Editor
{
    /// <summary>
    /// Excel转换工具
    /// </summary>
    public class ExcelConverter
    {
        private DataTable dataTable = null;

        public ExcelConverter(string excelFilePath)
        {
            // 检查文件是否存在
            if (string.IsNullOrEmpty(excelFilePath) || !File.Exists(excelFilePath))
            {
                Debug.LogError($"Excel file does not exist or path is invalid: {excelFilePath}");
                return;
            }

            LoadExcelData(excelFilePath);
        }

        /// <summary>
        /// 将Excel数据转换为JSON并写入文件
        /// </summary>
        /// <param name="jsonOutputPath">JSON文件输出路径</param>
        /// <param name="encoding">文件编码</param>
        public void ConvertToJson(string jsonOutputPath, Encoding encoding)
        {
            var idColumnIndex= -1;
            var idRowIndex = -1;
            var json = ConvertToJsonString(ref idColumnIndex, ref idRowIndex);
            WriteJsonToFile(json, jsonOutputPath, encoding);
        }

        /// <summary>
        /// 将Excel数据转换为JSON字符串
        /// </summary>
        /// <param name="idColumnIndex">ID列索引</param>
        /// <param name="idRowIndex">ID行索引</param>
        /// <returns>JSON字符串</returns>
        public string ConvertToJsonString(ref int idColumnIndex, ref int idRowIndex)
        {
            idColumnIndex = -1;
            idRowIndex = -1;

            // 判断Excel文件中是否存在数据表
            if (dataTable == null )
            {
                return "";
            }

            // 判断数据表内是否存在数据
            if (dataTable.Rows.Count < 1)
            {
                return "";
            }

            // 准备一个列表存储整个表的数据
            List<Dictionary<string, object>> tableData = new List<Dictionary<string, object>>();

            var fieldNameRowData = new List<object>();
            var fieldTypeRowData = new List<object>();

            // 第一行为备注，
            // 寻找到id字段行数，以下全为数据
            var skipRowCount = -1;
            var skipColCount = -1;

            // 这里skip 防止有人在 备注行直接输入id
            const int skipLine = 1;

            for (var i = skipLine; i < 10 && skipColCount == -1; i++)
            {
                var rows = GetRowData(i);
                // 遍历rows
                for (var j = 0; j < rows.Count; j++)
                {
                    if (!rows[j].Equals("ID")) continue;
                    skipRowCount = i;
                    skipColCount = j;
                    fieldNameRowData = rows;
                    // 获取字段类型
                    var rowDates = GetRowData(i - 1);
                    fieldTypeRowData = rowDates;
                }
            }

            if (skipRowCount == -1)
            {
                Debug.LogError("表格数据有错,没发现ID字段,请检查");
                return "{}";
            }

            idColumnIndex = skipColCount;
            idRowIndex = skipRowCount;

            // 读取数据
            for (var i = skipRowCount + 1; i < dataTable.Rows.Count; i++)
            {
                // 检查是否为空行
                if (IsRowEmpty(i, skipColCount))
                    continue;

                // 准备一个字典存储每一行的数据
                var row = new Dictionary<string, object>();

                for (var j = skipColCount; j < dataTable.Columns.Count; j++)
                {
                    // 检查是否为空列
                    if (IsColumnEmpty(j))
                        continue;

                    var field = fieldNameRowData[j].ToString();
                    // 跳过空字段
                    if (string.IsNullOrEmpty(field))
                        continue;

                    // Key-Value对应
                    var rowData = dataTable.Rows[i][j];
                    // 根据null判断
                    if (rowData == null)
                    {
                        Debug.LogErrorFormat("表格数据为空：[{0},{1}]", i, j);
                        continue;
                    }

                    var fieldType = fieldTypeRowData[j].ToString().ToLower();
                    if (rowData is DBNull) // 空类型判断，赋默认值
                    {
                        if (fieldType == "int" || fieldType == "float" || fieldType == "double")
                        {
                            row[field] = 0;
                        }
                        else if (fieldType == "string")
                        {
                            row[field] = "";
                        }
                        else if (fieldType == "bool")
                        {
                            row[field] = false;
                        }
                        else if (fieldType.Contains("[]")) // 空数组
                        {
                            row[field] = "[]";
                        }
                    }
                    else
                    {
                        // string数组，对单个元素加上""
                        if (fieldType == "string[]")
                        {
                            var value = rowData.ToString();
                            if (value != "[]" && !value.Contains("\"")) // 不是空数组,且没有""
                            {
                                if (value.StartsWith("\"["))
                                {
                                    value = value.Replace("\"[", "[\"");
                                    value = value.Replace("]\"", "\"]");
                                }
                                else
                                {
                                    value = value.Replace("[", "[\"");
                                    value = value.Replace("]", "\"]");
                                }

                                value = value.Replace(",", "\",\"");
                                row[field] = value;
                            }
                            else
                            {
                                row[field] = rowData;
                            }
                        }
                        // 其他数组 会被处理成string
                        else if (fieldType.Contains("["))
                        {
                            var value = rowData.ToString();
                            value = value.Replace("\"[", "[");
                            value = value.Replace("]\"", "]");
                            row[field] = value;
                        }
                        else if (fieldType == "int" || fieldType == "float" || fieldType == "double")
                        {
                            var oldValue = rowData.ToString();
                            if (fieldType == "int")
                            {
                                int value = 0;
                                if (int.TryParse(oldValue, out value))
                                {
                                    row[field] = value;
                                }
                                else
                                {
                                    row[field] = 0;
                                    Debug.LogErrorFormat("表格数据出错:{0}-{1}", i, j);
                                }
                            }
                            else if (fieldType == "float")
                            {
                                if (float.TryParse(oldValue, out var value))
                                {
                                    row[field] = value;
                                }
                                else
                                {
                                    row[field] = 0;
                                    Debug.LogErrorFormat("表格数据出错:{0}-{1}", i, j);
                                }
                            }
                            else if (fieldType == "double")
                            {
                                double value = 0;
                                if (double.TryParse(oldValue, out value))
                                {
                                    row[field] = value;
                                }
                                else
                                {
                                    row[field] = 0;
                                    Debug.LogErrorFormat("表格数据出错:{0}-{1}", i, j);
                                }
                            }
                        }
                        else if (field.Equals("string"))
                        {
                            row[field] = rowData.ToString();
                        }
                        else
                        {
                            row[field] = rowData;
                        }
                    }
                }

                // 添加到表数据中
                if (row.Count > 0)
                {
                    if (row.ContainsKey("ID") && (int)row["ID"] == 0)
                    {
                        continue;
                    }

                    tableData.Add(row);
                }
            }

            // 生成Json字符串
            string json = JsonMapper.ToJson(tableData);
            json = Regex.Unescape(json);
            // 把当字符串的数组 重新处理成数组
            json = json.Replace("\"[", "[").Replace("]\"", "]");
            json = json.Replace("\\\"", "\"");
            json = json.Replace("\"\"\"\"", "\"\"");
            return json;
        }
        
        #region Public Methods

        /// <summary>
        /// 检查指定行是否为空行
        /// </summary>
        /// <param name="rowIndex">行索引</param>
        /// <param name="startColumn">开始检查的列索引</param>
        /// <returns>是否为空行</returns>
        public bool IsRowEmpty(int rowIndex, int startColumn = 0)
        {
            // 判断Excel文件中是否存在数据表
            if (dataTable == null)
            {
                return true;
            }

            // 判断数据表内是否存在数据
            if (dataTable.Rows.Count <= rowIndex)
            {
                return true;
            }

            // 检查从startColumn开始的所有列是否都为空
            for (int columnIndex = startColumn; columnIndex < dataTable.Columns.Count; columnIndex++)
            {
                object item = dataTable.Rows[rowIndex][columnIndex];
                if (item != null && !(item is DBNull) && !string.IsNullOrEmpty(item.ToString().Trim()))
                {
                    return false; // 找到非空单元格，不是空行
                }
            }

            return true; // 所有检查的单元格都为空，是空行
        }

        /// <summary>
        /// 检查指定列是否为空列
        /// </summary>
        /// <param name="columnIndex">列索引</param>
        /// <returns>是否为空列</returns>
        public bool IsColumnEmpty(int columnIndex)
        {
            // 判断Excel文件中是否存在数据表
            if (dataTable == null)
            {
                return true;
            }

            // 判断数据表内是否存在数据
            if (dataTable.Columns.Count <= columnIndex)
            {
                return true;
            }

            // 检查该列的所有行是否都为空
            for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
            {
                object item = dataTable.Rows[rowIndex][columnIndex];
                if (item != null && !(item is DBNull) && !string.IsNullOrEmpty(item.ToString().Trim()))
                {
                    return false; // 找到非空单元格，不是空列
                }
            }

            return true; // 所有检查的单元格都为空，是空列
        }

        /// <summary>
        /// 获取指定行的数据
        /// </summary>
        /// <param name="rowIndex">行索引</param>
        /// <returns>行数据列表</returns>
        public List<object> GetRowData(int rowIndex)
        {
            List<object> rowDataList = new List<object>();

            // 判断Excel文件中是否存在数据表
            if (dataTable == null)
            {
                return rowDataList;
            }

            // 默认读取第一个数据表
            // 判断数据表内是否存在数据
            if (dataTable.Rows.Count <= rowIndex)
            {
                return rowDataList;
            }

            // 读取数据
            int columnCount = dataTable.Columns.Count;
            for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
            {
                object item = dataTable.Rows[rowIndex][columnIndex];
                rowDataList.Add(item);
            }

            return rowDataList;
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// 加载Excel文件数据
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径</param>
        private void LoadExcelData(string excelFilePath)
        {
            try
            {
                using (FileStream fileStream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
                    var resultSet = excelReader.AsDataSet();

                    if (resultSet != null && resultSet.Tables.Count > 0)
                    {
                        dataTable = resultSet.Tables[0];
                    }
                    else
                    {
                        Debug.LogError($"No data found in Excel file: {excelFilePath}");
                    }
                }
            }
            catch (Exception ex)
            {
                Debug.LogError($"Error reading Excel file {excelFilePath}: {ex.Message}");
            }
        }

        /// <summary>
        /// 将JSON字符串写入文件
        /// </summary>
        /// <param name="json">JSON字符串</param>
        /// <param name="jsonOutputPath">输出文件路径</param>
        /// <param name="encoding">文件编码</param>
        private void WriteJsonToFile(string json, string jsonOutputPath, Encoding encoding)
        {
            try
            {
                using (FileStream fileStream = new FileStream(jsonOutputPath, FileMode.Create, FileAccess.Write))
                using (TextWriter textWriter = new StreamWriter(fileStream, encoding))
                {
                    textWriter.Write(json);
                    Debug.Log($"{jsonOutputPath} Json文件写入成功");
                }
            }
            catch (Exception ex)
            {
                Debug.LogError($"Error writing JSON file {jsonOutputPath}: {ex.Message}");
            }
        }
        

        #endregion
    }
}